/*
 * Copyright (C) 2014 www.htwater.net
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.miao.photoprinter.database;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import cn.miao.photoprinter.entity.FavPhoto;
import cn.miao.photoprinter.entity.PhotoCheckItem;
import cn.miao.photoprinter.entity.ShoppingCart;
import cn.miao.photoprinter.entity.ShoppingRecord;

/**
 * 
 * @author nealmiao
 * @version
 * @since v 1.0
 * @Date 2014年12月1日 下午2:57:03
 */
public class DatabaseHelper extends SQLiteOpenHelper {
	static String name = "photoprint.db";
	static int dbVersion = 2;

	public DatabaseHelper(Context context) {
		super(context, name, null, dbVersion);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table shoppingrecord("
				+ "id integer primary key autoincrement,"
				+ "shopname varchar(20),photosize varchar(20),"
				+ "photomaterial varchar(20),photobrand varchar(20),"
				+ "others varchar(20),photocnt integer,photos varchar(2000),address varchar(2000))";
		db.execSQL(sql);
		String sql2 = "create table favphoto("
				+ "id integer primary key autoincrement,"
				+ "photopath varchar(2000),photodate varchar(20))";
		db.execSQL(sql2);
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists shoppingrecord");
		db.execSQL("drop table if exists favphoto");
		onCreate(db);
		// super.onDowngrade(db, oldVersion, newVersion);
	}
	
	public int getCartCount() {
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.query("shoppingrecord", new String[] {"shopname"}, "address is null", null, null, null, null);
		return cursor.getCount();
	}

	public long insertData(ShoppingRecord cart) {
		SQLiteDatabase db = getWritableDatabase();
		// db.execSQL("insert into hero(shopname,shopname,photosize,photomaterial,photobrand) values('xx',3,5,7)");
		ContentValues values = new ContentValues();
		values.put("shopname", cart.getShop());
		values.put("photosize", cart.getPhotoSize());
		values.put("photomaterial", cart.getPhotoMatieral());
		values.put("photobrand", cart.getPhotoBrand());
		values.put("others", cart.getOthers());
		values.put("photocnt", cart.getPhotocnt());
		values.put("photos", cart.getPhotos());
		long result = db.insert("shoppingrecord", "id", values);
		db.close();
		return result;
	}
	
	public List<ShoppingCart> queryShoppingCart() {
		List<ShoppingCart> list = new ArrayList<ShoppingCart>();
		ShoppingCart record = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query("shoppingrecord", null, null, null, null, null, null);
		for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
			record = new ShoppingCart();
			record.setId(cursor.getInt(cursor.getColumnIndex("id")));
			record.setShop(cursor.getString(cursor.getColumnIndex("shopname")));
			record.setPhotoSize(cursor.getString(cursor.getColumnIndex("photosize")));
			record.setPhotoMatieral(cursor.getString(cursor.getColumnIndex("photomaterial")));
			record.setOthers(cursor.getString(cursor.getColumnIndex("others")));
			record.setPhotoBrand(cursor.getString(cursor.getColumnIndex("photobrand")));
			record.setPhotos(cursor.getString(cursor.getColumnIndex("photos")));
			record.setPhotocnt(cursor.getInt(cursor.getColumnIndex("photocnt")));
			list.add(record);
		}
		cursor.close();
		db.close();
		return list;
	}

	public List<ShoppingRecord> queryData() {
		List<ShoppingRecord> list = new ArrayList<ShoppingRecord>();
		ShoppingCart record = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query("shoppingrecord", null, null, null, null, null, null);
		for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
			record = new ShoppingCart();
			record.setId(cursor.getInt(cursor.getColumnIndex("id")));
			record.setShop(cursor.getString(cursor.getColumnIndex("shopname")));
			record.setPhotoSize(cursor.getString(cursor.getColumnIndex("photosize")));
			record.setPhotoMatieral(cursor.getString(cursor.getColumnIndex("photomaterial")));
			record.setOthers(cursor.getString(cursor.getColumnIndex("others")));
			record.setPhotoBrand(cursor.getString(cursor.getColumnIndex("photobrand")));
			record.setPhotos(cursor.getString(cursor.getColumnIndex("photos")));
			record.setPhotocnt(cursor.getInt(cursor.getColumnIndex("photocnt")));
			list.add(record);
		}
		cursor.close();
		db.close();
		return list;
	}

	public long updateData(String address, int id) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("address", address);
		long result = db.update("shoppingrecord", values, "id = " + id, null);
		db.close();
		return result;
	}

	public void deleteData(int id) {
		SQLiteDatabase db = getWritableDatabase();
		db.delete("shoppingrecord", "id = " + id, null);
		db.close();
	}
	
	public long insertFavPhoto(FavPhoto photo) {
		SQLiteDatabase db = getWritableDatabase();
		// db.execSQL("insert into hero(shopname,shopname,photosize,photomaterial,photobrand) values('xx',3,5,7)");
		ContentValues values = new ContentValues();
		values.put("photopath", photo.getPath());
		values.put("photodate", photo.getDate());
		long result = db.insert("favphoto", "id", values);
		db.close();
		return result;
	}
	
	public List<PhotoCheckItem> queryFavPhoto() {
		List<PhotoCheckItem> list = new ArrayList<PhotoCheckItem>();
		PhotoCheckItem record = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query("favphoto", null, null, null, null, null, null);
		for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
			record = new PhotoCheckItem();
			record.setPath(cursor.getString(cursor.getColumnIndex("photopath")));
			// record.setCheck(false);// (cursor.getString(cursor.getColumnIndex("photodate")));
			list.add(record);
		}
		cursor.close();
		db.close();
		return list;
	}
	
	public Map<String, Integer> queryFavPhotoSection() {
		Map<String, Integer> sectionInfo = new LinkedHashMap<String, Integer>();
		SQLiteDatabase db = getReadableDatabase();
		// Cursor cursor = db.query("favphoto", new String[] {"photodate, COUNT(*) AS cnt"}, null, null, "(0==0) GROUP BY (photodate)", null, null);
		Cursor cursor = db.rawQuery("select photodate, count(*) as cnt from favphoto group by photodate", null);
		for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
			sectionInfo.put(cursor.getString(cursor.getColumnIndex("photodate")), cursor.getInt(cursor.getColumnIndex("cnt")));
		}
		cursor.close();
		db.close();
		return sectionInfo;
	}
}
